package com.nercel.dsj.gksales.util;

/**
 * Mysql Utilities
 *
 * @author Ralph Ritoch <rritoch@gmail.com>
 * @copyright Ralph Ritoch 2011 ALL RIGHTS RESERVED
 * @link http://www.vnetpublishing.com
 */

public class MySQLUtil {

	/**
	 * Escape string to protected against SQL Injection
	 * <p>
	 * You must add a single quote ' around the result of this function for data,
	 * or a backtick ` around table and row identifiers.
	 * If this function returns null than the result should be changed
	 * to "NULL" without any quote or backtick.
	 *
	 * @param link
	 * @param str
	 * @return
	 * @throws Exception
	 */

	public static String mysqlRealEscapeString(java.sql.Connection link, String str)
			throws Exception {
		if (str == null) {
			return null;
		}
		if (str.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/? ]", "").length() < 1) {
			return str;
		}
		String cleanString = str;
		cleanString = cleanString.replaceAll("\\\\", "\\\\\\\\");
		cleanString = cleanString.replaceAll("\\n", "\\\\n");
		cleanString = cleanString.replaceAll("\\r", "\\\\r");
		cleanString = cleanString.replaceAll("\\t", "\\\\t");
		cleanString = cleanString.replaceAll("\\00", "\\\\0");
		cleanString = cleanString.replaceAll("'", "\\\\'");
		cleanString = cleanString.replaceAll("\\\"", "\\\\\"");
		if (cleanString.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/?\\\\\"' ]"
				, "").length() < 1) {
			return cleanString;
		}
		java.sql.Statement stmt = link.createStatement();
		String qry = "SELECT QUOTE('" + cleanString + "')";
		stmt.executeQuery(qry);
		java.sql.ResultSet resultSet = stmt.getResultSet();
		resultSet.first();
		String r = resultSet.getString(1);
		return r.substring(1, r.length() - 1);
	}


	public static String mysqlRealEscapeString(String str) {
		if (str == null) {
			return null;
		}
		if (str.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/? ]", "").length() < 1) {
			return str;
		}
		String cleanString = str;
		cleanString = cleanString.replaceAll("\\\\", "\\\\\\\\");
		cleanString = cleanString.replaceAll("\\n", "\\\\n");
		cleanString = cleanString.replaceAll("\\r", "\\\\r");
		cleanString = cleanString.replaceAll("\\t", "\\\\t");
		cleanString = cleanString.replaceAll("\\00", "\\\\0");
		cleanString = cleanString.replaceAll("'", "\\\\'");
		cleanString = cleanString.replaceAll("\\\"", "\\\\\"");
//		if (cleanString.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/?\\\\\"' ]"
//				, "").length() < 1) {
//			return cleanString;
//		}
		return cleanString;
	}

	/**
	 * Escape data to protected against SQL Injection
	 *
	 * @param link
	 * @param str
	 * @return
	 * @throws Exception
	 */

	public static String quote(java.sql.Connection link, String str)
			throws Exception {
		if (str == null) {
			return "NULL";
		}
		return "'" + mysqlRealEscapeString(link, str) + "'";
	}

	/**
	 * Escape identifier to protected against SQL Injection
	 *
	 * @param link
	 * @param str
	 * @return
	 * @throws Exception
	 */

	public static String nameQuote(java.sql.Connection link, String str)
			throws Exception {
		if (str == null) {
			return "NULL";
		}
		return "`" + mysqlRealEscapeString(link, str) + "`";
	}


	public static void main(String args[]) throws Exception {
		String sql = "abc\r\nxyz''中国";
		sql = sql.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/?\\\\\"' ]"
				, "");
		System.out.println(sql);
	}
}